﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace WZS_Package
{
    public class MySql
    {
        public string Database { get; set; }
        public string DataSource { get; set; }
        public string UserId { get; set; }
        public string Password { get; set; }
        public string charset { get; set; }
        public bool Polling { get; set; }
        public string port { get; set; }

        public MySql()
        {
            Database = "data";
            DataSource = "127.0.0.1";
            UserId = "yep";
            Password = "666666";
            charset = "utf8";
            Polling = true;
            port = "3306";
        }

        Mutex lockgetDataset = new Mutex();

        /// <summary>
        /// 建立mysql数据库链接
        /// </summary>
        /// <returns></returns>
        ///  "Database=longhorndbs;Data Source=127.0.0.1;User Id=root;Password=cygia123;pooling=false;CharSet=utf8;port=3306";
        public MySqlConnection getMySqlConnection()
        {
            String mysqlStr = "Database=" + Database + ";Data Source=" + DataSource + ";User Id=" + UserId + ";Password=" + Password + ";pooling=" + Polling + ";CharSet=" + charset + ";port=" + port;
            // String mySqlCon = ConfigurationManager.ConnectionStrings["MySqlCon"].ConnectionString;
            MySqlConnection mysql = new MySqlConnection(mysqlStr);
            return mysql;
        }

        /// <summary>
        /// 建立执行命令语句对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="mysql"></param>
        /// <returns></returns>
        public MySqlCommand getSqlCommand(String sql, MySqlConnection mysql)
        {
            MySqlCommand mySqlCommand = new MySqlCommand(sql, mysql);
            //  MySqlCommand mySqlCommand = new MySqlCommand(sql);
            // mySqlCommand.Connection = mysql;
            return mySqlCommand;
        }
        public MySqlDataAdapter getSqlDataAdapter(String sql, MySqlConnection mysql)
        {
            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, mysql);
            return mySqlDataAdapter;
        }

        /// <summary>
        /// 查询并获得结果返回DataTable
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public DataTable runSQL_getDataTable(MySqlCommand mySqlCommand, out string errorMessage)
        {
            MySqlDataReader reader = mySqlCommand.ExecuteReader();
            DataTable dt = new DataTable();
            List<string> columnName = new List<string>();
            try
            {
                dt.Load(reader);
                int a = dt.Rows.Count;//获取datable行数
                DataRow myDr = dt.Rows[0];//这是拿到第一行数据

                //下面是第一行的字段值取法
                string myValue1 = myDr[0].ToString();

                foreach (DataColumn k in dt.Columns)
                {
                    columnName.Add(k.ColumnName.ToString());//获取datable 列名和数据类型
                    //columnType = k.DataType.ToString();
                }
                errorMessage = "";
                return dt;
            }
            catch (Exception ex)
            {
                errorMessage="["+MethodBase.GetCurrentMethod().Name+"]"+ex.ToString();
               
                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"ex.ToString(), 0, LogItem.Error);
                return dt;
            }
            finally
            {
                reader.Close();
            }
        }
        /// <summary>
        /// 查询数据返回DataSet
        /// </summary>
        /// <param name="SqlDataAdapter"></param>
        public DataSet runSQL_getDataset(string Sql, out string errorMessage)//根据sql语句获取一个DataSet包含表格内所有数据
        {
            lockgetDataset.WaitOne();
            MySqlConnection mysql = getMySqlConnection();
            MySqlDataAdapter SqlDataAdapter = new MySqlDataAdapter(Sql, mysql);
            DataSet ds = new DataSet();
            try
            {
                mysql.Open();
                SqlDataAdapter.Fill(ds);
                mysql.Close();
                lockgetDataset.ReleaseMutex();
                errorMessage = "";
                return ds;
            }
            catch (Exception ex)
            {
                lockgetDataset.ReleaseMutex();
                mysql.Close();
                errorMessage = "[" + MethodBase.GetCurrentMethod().Name + "]" + "run sql:" + Sql + ex.ToString();
                
                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"ex.ToString(), 0, LogItem.Error);
                String message = ex.Message;
                return ds;
            }

        }
        /// <summary>
        /// 查询列名
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public List<string> getSqlColumnName( string tableName, out string errorMessage)
        {
            MySqlConnection mysql = getMySqlConnection();
            List<string> columnName = new List<string>();
            string sql = "select column_name from information_schema.columns where table_name='" + tableName + "' ";
            try
            {
                mysql.Open();
                MySqlCommand mySqlCommand = getSqlCommand(sql, mysql);
                //SqlCommand com = new SqlCommand(sql, mysql);
                MySqlDataReader reader = mySqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    columnName.Add(reader[0].ToString());
                }
                reader.Close();
                mysql.Close();
                errorMessage = "";
            }
            catch (Exception ex)
            {
                errorMessage = "["+MethodBase.GetCurrentMethod().Name+"]"+ex.ToString();
                
                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"ex.ToString(), 0, LogItem.Error);
            }

            return columnName;
        }


        /// <summary>
        /// 根据sql语句查询出对应的数据 列名加值组成的dictionary list
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public List<Dictionary<string, string>> getSqlDictionaryList(string tableName, string sql, out string errorMessage)
        {
            List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
            string errorMessage1 = "";
            string errorMessage2 = "";
            List<string> columnName = getSqlColumnName(tableName,out errorMessage1);
           
            try
            {
               DataSet ds= runSQL_getDataset(sql,out errorMessage2);
               for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
               {
                   Dictionary<string, string> dict = new Dictionary<string, string>();
                  
                   for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                   {                       
                       dict.Add(columnName[j], ds.Tables[0].Rows[i][j].ToString());
                       if (j==ds.Tables[0].Columns.Count-1)
                       {
                           result.Add(dict);
                       }
                   }
               }
                errorMessage = "";
            }
            catch (Exception ex)
            {
                errorMessage =errorMessage1+errorMessage2+ "[" + MethodBase.GetCurrentMethod().Name + "]"+"run sql:"+sql + ex.ToString();

                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"ex.ToString(), 0, LogItem.Error);
            }

            return result;
        }


        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="sql"></param>
        ///  /// 例子1 string.Format("insert into tableName (field1,field2) values('{0}','{1}')",data1, data2)
        /// 例子2 "insert into tableName (field1,field2) values('values1','values2')"
        public void InsertData(string sql, out string errorMessage)
        {
            try
            {
                MySqlConnection mysql = getMySqlConnection();
                MySqlCommand mySqlCommand = getSqlCommand(sql, mysql);
                mysql.Open();
                mySqlCommand.ExecuteNonQuery();
                mysql.Close();
                errorMessage = "";
            }
            catch (Exception ex)
            {
                errorMessage = "[" + MethodBase.GetCurrentMethod().Name + "]" + "run sql:" + sql + ex.ToString();
                
                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"ex.ToString(), 0, LogItem.Error);

            }

        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="sql"></param>
        public void UpdateData(string sql, out string errorMessage)
        {
            try
            {
                MySqlConnection mysql = getMySqlConnection();
                MySqlCommand mySqlCommand = getSqlCommand(sql, mysql);
                mysql.Open();
                mySqlCommand.ExecuteNonQuery();
                mysql.Close();
                errorMessage = "";
            }
            catch (Exception ex)
            {
                errorMessage = "[" + MethodBase.GetCurrentMethod().Name + "]" + "run sql:" + sql + ex.ToString();
                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"ex.ToString(), 0, LogItem.Error);

            }
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public void DelData(string sql, out string errorMessage)
        {
            try
            {
                MySqlConnection mysql = getMySqlConnection();
                MySqlCommand mySqlCommand = getSqlCommand(sql, mysql);
                mysql.Open();
                mySqlCommand.ExecuteNonQuery();
                mysql.Close();
                errorMessage = "";
            }
            catch (Exception ex)
            {
                errorMessage = "[" + MethodBase.GetCurrentMethod().Name + "]" +"run sql:"+sql+ ex.ToString();
                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"ex.ToString(), 0, LogItem.Error);

            }
        }
        public int getDataNumber(string tableName, out string errorMessage)//获取表格的数据数目，也可以说是行数
        {
            MySqlConnection mysql = getMySqlConnection();
            try
            {
                string sqlMsg = "select count(*)as cnt from " + tableName;
                MySqlDataAdapter mySqlCommand = getSqlDataAdapter(sqlMsg, mysql);
                mysql.Open();
                DataSet ds = new DataSet();
                mySqlCommand.Fill(ds);
                mysql.Close();
                errorMessage = "";
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            catch (Exception ex)
            {
                mysql.Close();
                errorMessage = "[" + MethodBase.GetCurrentMethod().Name + "]" + ex.ToString();
                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"+ex.ToString(), 0, LogItem.Error);

                return -1;
            }
        }
        public void deleteFirstLineData(string schemaName)//删除第一行数据
        {
            MySqlConnection mysql = getMySqlConnection();
            try
            {
                String sqlDel = "delete from " + schemaName + " limit 1";
                string errorMessage = "";
                DelData(sqlDel,out errorMessage);

            }
            catch (Exception ex)
            {              
                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"+ex.ToString(), 0, LogItem.Error);
                return;
            }
        }
        /// <summary>
        /// 获取字符串中的数字
        /// </summary>
        /// <param name="str">字符串</param>
        /// <returns>数字</returns>
        public int GetNumberInt(string str)
        {
            int result = 0;
            if (str != null && str != string.Empty)
            {
                // 正则表达式剔除非数字字符（不包含小数点.）
                str = Regex.Replace(str, @"[^\d.\d]", "");
                // 如果是数字，则转换为decimal类型
                if (Regex.IsMatch(str, @"^[+-]?\d*[.]?\d*$"))
                {
                    result = int.Parse(str);
                }
            }
            return result;
        }
        public DataSet get_message_from_table(string tableName)
        {
            DataSet ds = new DataSet();
            try
            {
                string sql = "select * from  " + tableName;
                string errorMessage = "";
                ds = runSQL_getDataset(sql,out errorMessage);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"+ex.ToString(), 0, LogItem.Error);
            }
            return ds;
        }

        public Dictionary<string, string> get_values_number_dictionary(string tableName,bool isHaveID)//因为sql语句一项项输入列名和数字太麻烦了，写个方法来偷懒
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            DataSet ds = new DataSet();
            try
            {
                string ColumNames = "";
                string itemNumbers = "";
                ds = get_message_from_table(tableName);
             
                if (isHaveID)
                {
                    foreach (var item in ds.Tables[0].Columns)
                    {
                        ColumNames += item.ToString() + ",";
                    }

                    for (int i = 0; i < ds.Tables[0].Columns.Count-1; i++)
                    {
                        itemNumbers += "'{" + i.ToString() + "}',";
                    }
                    dict["ColumNames"] = ColumNames.TrimEnd(',').Remove(0,3);//ColumNames.TrimEnd(',') 删除最后多余的一个，
                    dict["itemNumbers"] = itemNumbers.TrimEnd(',');
                }
                else
                {
                    foreach (var item in ds.Tables[0].Columns)
                    {
                        ColumNames += item.ToString() + ",";
                    }
                    
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        itemNumbers += "'{" + i.ToString() + "}',";
                    }
                    dict["ColumNames"] = ColumNames.TrimEnd(',');//ColumNames.TrimEnd(',') 删除最后多余的一个，
                    dict["itemNumbers"] = itemNumbers.TrimEnd(',');
                }
                return dict;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return dict;

                //GMgr.UIView.LogWrite("["+MethodBase.GetCurrentMethod().Name+"]"+ex.ToString(), 0, LogItem.Error);

            }
        }      

    }
}
